Compute site visits

This notebook computes the number of visits at each sample site from an excel file exported from an online database (arcgis online) of site visits.

Required pacakges

Variable declarations

excel_filepath – path to excel file with site visit records
csv_output_filepath – path to export csv file


In [1]:
excel_filepath = ""

In [2]:
csv_output_filepath = ""

Import statements


In [1]:
import pandas

Compute site visits


In [2]:
file = pandas.read_excel(excel_filepath)

In [3]:
file = file.rename_axis({'NAME':'Name', 'TIME1':'Time', 'WEATHER1':'Weather', 'TEMPERATURE1':'Temperature'}, 1)

In [4]:
data = file[['ID', 'Name', 'Time', 'Weather', 'Temperature']].sort_values(by=['ID', 'Name', 'Time'])

In [5]:
data['ID'] = data['ID'].map('{:g}'.format)

In [6]:
data['Name'] = [n.strip() for n in data['Name']]

In [7]:
counts = data['ID'].value_counts().sort_index()

In [8]:
names = data[['ID', 'Name']].drop_duplicates().set_index('ID')

In [9]:
count = names.join(counts).rename_axis({'ID':'count'}, 1)

In [10]:
count


Out[10]:
Name count
ID
1 Lans 5
2 Hofwald 5
3 Arzler Straße 4
4 Kranebitten 4
5 Gumppstraße 5
6 Pfaffensteig 3
7 Höttinger Rain 5
8 Igls 5
9 Golfcourse B 4
10 Klosterberg 4
11 Ullwald 4
12 Alpenzoo 4
13 Andreas-Hofer 5
14 Pumhof 3
15 Hofgarten 5
16 Vill 4
17 Golfcourse A 4
18 Egger-Lienz-Straße 5
19 Schillerweg 4
20 Wilten 5
21 Templstraße 5
22 Hungerburg 4
23 Poltenweg 4
24 Rechenhof 4
25 Buzihütte 4
26 Bienenstraße 5
27 Sadrach 4
28 Brenner 4
29 Rossau 3
30 Wittenberg 5
101 Sternwarte 1
102 Schießstand 2
103 Kerschbuchhof 3
104 Hungerburg 1
105 Klamm 1
106 Mentlberg 2
107 Zur Eiche 4
108 Andreas-Hofer 4
109 Handlhof 1
110 Poltenweg 2
111 SchlossAmbras 1
112 Bichl 1
nan 1

In [11]:
data


Out[11]:
ID Name Time Weather Temperature
26 1 Lans 2016-02-24 15:00:00 Sunny 6.0
58 1 Lans 2016-03-09 15:03:15 Sunny and windy, light dusting of snow (~2cm) 3.0
100 1 Lans 2016-04-12 15:00:00 Mostly sunny 15.0
128 1 Lans 2016-04-21 15:00:00 sunny 22.0
140 1 Lans 2016-04-27 15:00:00 partly sunny 5.0
6 2 Hofwald 2016-02-18 15:00:00 Mostly sunny 4.0
43 2 Hofwald 2016-03-03 15:00:00 Cloudy 4.0
62 2 Hofwald 2016-03-15 15:00:00 Snowing 2.0
82 2 Hofwald 2016-04-05 15:00:00 Cloudy and breezy 21.0
134 2 Hofwald 2016-04-26 15:00:00 mostly cloudy 7.0
20 3 Arzler Straße 2016-02-22 16:30:00 Mostly cloudy / overcast 14.0
53 3 Arzler Straße 2016-03-07 16:30:30 Cloudy and snowing 2.0
81 3 Arzler Straße 2016-04-04 16:30:00 Mostly sunny, windy 21.0
116 3 Arzler Straße 2016-04-18 16:30:00 cloudy, drizzle 8.0
7 4 Kranebitten 2016-02-18 16:00:00 Partly sunny 4.0
45 4 Kranebitten 2016-03-03 16:00:00 Cloudy 4.0
84 4 Kranebitten 2016-04-05 16:00:00 Cloudy and breezy 22.0
136 4 Kranebitten 2016-04-26 16:00:00 mostly cloudy 7.0
3 5 Gumppstraße 2016-02-17 16:00:00 Mostly cloudy 6.0
38 5 Gumppstraße 2016-03-02 16:00:00 Cloudy 8.0
66 5 Gumppstraße 2016-03-16 16:00:00 Sunny, occasional breeze 10.0
90 5 Gumppstraße 2016-04-06 16:00:00 Calm and cloudy 13.0
124 5 Gumppstraße 2016-04-20 16:00:00 sunny 15.0
57 6 Pfaffensteig 2016-03-08 17:45:00 Cloudy 2.0
109 6 Pfaffensteig 2016-04-13 17:45:00 Mostly cloudy, storm approaching 17.0
122 6 Pfaffensteig 2016-04-19 17:45:00 mostly sunny 14.0
10 7 Höttinger Rain 2016-02-18 17:45:00 Cloudy 3.0
42 7 Höttinger Rain 2016-03-03 17:45:00 Cloudy with flurries 3.0
63 7 Höttinger Rain 2016-03-15 17:45:00 Cloudy, just stopped snowing 1.0
86 7 Höttinger Rain 2016-04-05 17:45:00 Cloudy and breezy 22.0
... ... ... ... ... ...
61 29 Rossau 2016-03-14 14:36:00 Mostly sunny, slight breeze 9.0
30 30 Wittenberg 2016-03-01 15:30:00 Flurries and cloudy, light dusting of snow (~1cm) 1.0
70 30 Wittenberg 2016-03-17 15:30:00 Sunny and calm 14.0
96 30 Wittenberg 2016-04-11 15:30:00 Sunny and breezy 20.0
147 30 Wittenberg 2016-04-29 15:30:00 sunny 15.0
11 30 Wittenberg 2016-02-19 15:30:00 Lightly snowing 6.0
60 101 Sternwarte 2016-03-11 12:00:00 Mostly sunny 8.0
0 102 Schießstand 2016-01-27 15:15:00 NaN NaN
133 102 Schießstand 2016-04-25 15:15:00 partly sunny 10.0
44 103 Kerschbuchhof 2016-03-03 15:45:00 Cloudy 4.0
83 103 Kerschbuchhof 2016-04-05 15:45:00 Cloudy and breezy 22.0
135 103 Kerschbuchhof 2016-04-26 15:45:00 mostly cloudy 7.0
47 104 Hungerburg 2016-03-07 13:45:00 Cloudy and lightly snowing, slight dusting of ... 4.0
16 105 Klamm 2016-02-22 14:30:00 Sunny 15.0
144 106 Mentlberg 2016-04-29 14:00:00 sunny 15.0
93 106 Mentlberg 2016-04-11 14:00:00 Sunny and calm 19.0
34 107 Zur Eiche 2016-03-01 14:50:00 Flurries and cloudy, light dusting of snow (~5cm) 1.0
69 107 Zur Eiche 2016-03-17 14:50:00 Sunny and calm 14.0
95 107 Zur Eiche 2016-04-11 14:50:00 Sunny and breezy 20.0
146 107 Zur Eiche 2016-04-29 14:50:00 sunny 15.0
31 108 Andreas-Hofer 2016-03-01 16:10:00 Cloudy with peaks of sun, light dusting of sno... 1.0
72 108 Andreas-Hofer 2016-03-17 16:10:00 Sunny and calm 15.0
98 108 Andreas-Hofer 2016-04-11 16:10:00 Sunny and breezy 21.0
149 108 Andreas-Hofer 2016-04-29 16:10:00 sunny 15.0
104 109 Handlhof 2016-04-12 16:45:00 Mostly sunny 17.0
118 110 Poltenweg 2016-04-19 15:30:00 mostly sunny 14.0
106 110 Poltenweg 2016-04-13 15:30:00 Partly sunny 20.0
25 111 SchlossAmbras 2016-02-23 17:15:00 Mostly cloudy, breezy 10.0
127 112 Bichl 2016-04-21 14:30:00 sunny 22.0
152 nan NaT NaN NaN

153 rows × 5 columns

Export to csv


In [12]:
data.to_csv(csv_output_filepath)